{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Week 2 Day 5 Exercise - Radio Africa Products Chatbot\n",
        "\n",
        "\n",
        "This chatbot provides comprehensive information about Radio Africa Products, including:\n",
        "- **Career Opportunities**: View and manage job openings\n",
        "- **Radio Station Costs**: Get and set advertising costs for 5 radio stations\n",
        "- **Database Integration**: Persistent storage with SQLite (ral.db)\n",
        "\n",
        "### Radio Stations:\n",
        "- **Kiss FM**: Kenya's leading urban radio station\n",
        "- **Classic 105**: Kenya's premier classic hits station  \n",
        "- **Radio Jambo**: Kenya's most popular vernacular station\n",
        "- **Homeboyz Radio**: Kenya's youth-focused radio station\n",
        "- **Gukena FM**: Kenya's leading vernacular station\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {},
      "outputs": [],
      "source": [
        "# Import necessary libraries\n",
        "import os\n",
        "import json\n",
        "import sqlite3\n",
        "from dotenv import load_dotenv\n",
        "from openai import OpenAI\n",
        "import gradio as gr\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "OpenAI API Key exists and begins sk-proj-\n",
            "✅ Radio Africa Products Assistant initialized!\n"
          ]
        }
      ],
      "source": [
        "# Initialize OpenAI client\n",
        "load_dotenv(override=True)\n",
        "\n",
        "openai_api_key = os.getenv('OPENAI_API_KEY')\n",
        "if openai_api_key:\n",
        "    print(f\"OpenAI API Key exists and begins {openai_api_key[:8]}\")\n",
        "else:\n",
        "    print(\"OpenAI API Key not set\")\n",
        "    \n",
        "MODEL = \"gpt-4o-mini\"\n",
        "openai = OpenAI()\n",
        "\n",
        "# Database setup\n",
        "DB = \"ral.db\"\n",
        "\n",
        "# System message for the Radio Africa assistant\n",
        "system_message = \"\"\"\n",
        "You are a helpful assistant for Radio Africa Products, a leading media company in Kenya.\n",
        "You can provide information about:\n",
        "- Career opportunities at Radio Africa\n",
        "- Advertising costs for our 5 radio stations (Kiss FM, Classic 105, Radio Jambo, Homeboyz Radio, Gukena FM)\n",
        "- Spot ad costs and sponsorship costs for each station\n",
        "- General information about Radio Africa Products\n",
        "\n",
        "Give helpful, accurate answers. If you don't know something, say so.\n",
        "Keep responses concise but informative.\n",
        "\"\"\"\n",
        "\n",
        "print(\"✅ Radio Africa Products Assistant initialized!\")\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "✅ Radio Africa database setup complete!\n"
          ]
        }
      ],
      "source": [
        "# Database setup\n",
        "def setup_database():\n",
        "    \"\"\"Initialize the database with required tables\"\"\"\n",
        "    with sqlite3.connect(DB) as conn:\n",
        "        cursor = conn.cursor()\n",
        "        \n",
        "        # Radio stations table\n",
        "        cursor.execute('''\n",
        "            CREATE TABLE IF NOT EXISTS radio_stations (\n",
        "                id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "                name TEXT UNIQUE NOT NULL,\n",
        "                spot_ad_cost REAL NOT NULL,\n",
        "                sponsorship_cost REAL NOT NULL,\n",
        "                description TEXT\n",
        "            )\n",
        "        ''')\n",
        "        \n",
        "        # Career opportunities table\n",
        "        cursor.execute('''\n",
        "            CREATE TABLE IF NOT EXISTS career_opportunities (\n",
        "                id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "                title TEXT NOT NULL,\n",
        "                department TEXT NOT NULL,\n",
        "                description TEXT,\n",
        "                requirements TEXT,\n",
        "                salary_range TEXT,\n",
        "                location TEXT,\n",
        "                is_active BOOLEAN DEFAULT 1\n",
        "            )\n",
        "        ''')\n",
        "        \n",
        "        conn.commit()\n",
        "    print(\"✅ Radio Africa database setup complete!\")\n",
        "\n",
        "# Setup the database\n",
        "setup_database()\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "✅ Tool functions defined!\n"
          ]
        }
      ],
      "source": [
        "# Tool functions\n",
        "def get_radio_station_costs(station_name):\n",
        "    \"\"\"Get advertising costs for a specific radio station\"\"\"\n",
        "    print(f\"DATABASE TOOL CALLED: Getting costs for {station_name}\", flush=True)\n",
        "    with sqlite3.connect(DB) as conn:\n",
        "        cursor = conn.cursor()\n",
        "        cursor.execute('SELECT name, spot_ad_cost, sponsorship_cost, description FROM radio_stations WHERE name LIKE ?', (f'%{station_name}%',))\n",
        "        result = cursor.fetchone()\n",
        "        if result:\n",
        "            return f\"Station: {result[0]}\\nSpot Ad Cost: KSh {result[1]:,}\\nSponsorship Cost: KSh {result[2]:,}\\nDescription: {result[3]}\"\n",
        "        else:\n",
        "            return f\"No information found for {station_name}. Available stations: Kiss FM, Classic 105, Radio Jambo, Homeboyz Radio, Gukena FM\"\n",
        "\n",
        "def set_radio_station_costs(station_name, spot_ad_cost, sponsorship_cost):\n",
        "    \"\"\"Set advertising costs for a specific radio station\"\"\"\n",
        "    print(f\"DATABASE TOOL CALLED: Setting costs for {station_name}\", flush=True)\n",
        "    with sqlite3.connect(DB) as conn:\n",
        "        cursor = conn.cursor()\n",
        "        cursor.execute('''\n",
        "            UPDATE radio_stations \n",
        "            SET spot_ad_cost = ?, sponsorship_cost = ?\n",
        "            WHERE name LIKE ?\n",
        "        ''', (spot_ad_cost, sponsorship_cost, f'%{station_name}%'))\n",
        "        \n",
        "        if cursor.rowcount > 0:\n",
        "            conn.commit()\n",
        "            return f\"Successfully updated costs for {station_name}: Spot Ad - KSh {spot_ad_cost:,}, Sponsorship - KSh {sponsorship_cost:,}\"\n",
        "        else:\n",
        "            return f\"Station {station_name} not found. Available stations: Kiss FM, Classic 105, Radio Jambo, Homeboyz Radio, Gukena FM\"\n",
        "\n",
        "def get_career_opportunities(department=None):\n",
        "    \"\"\"Get career opportunities, optionally filtered by department\"\"\"\n",
        "    print(f\"DATABASE TOOL CALLED: Getting career opportunities for {department or 'all departments'}\", flush=True)\n",
        "    with sqlite3.connect(DB) as conn:\n",
        "        cursor = conn.cursor()\n",
        "        if department:\n",
        "            cursor.execute('''\n",
        "                SELECT title, department, description, requirements, salary_range, location \n",
        "                FROM career_opportunities \n",
        "                WHERE department LIKE ? AND is_active = 1\n",
        "            ''', (f'%{department}%',))\n",
        "        else:\n",
        "            cursor.execute('''\n",
        "                SELECT title, department, description, requirements, salary_range, location \n",
        "                FROM career_opportunities \n",
        "                WHERE is_active = 1\n",
        "            ''')\n",
        "        \n",
        "        results = cursor.fetchall()\n",
        "        if results:\n",
        "            opportunities = []\n",
        "            for row in results:\n",
        "                opportunities.append(f\"Title: {row[0]}\\nDepartment: {row[1]}\\nDescription: {row[2]}\\nRequirements: {row[3]}\\nSalary: {row[4]}\\nLocation: {row[5]}\\n\")\n",
        "            return \"\\n\".join(opportunities)\n",
        "        else:\n",
        "            return f\"No career opportunities found for {department or 'any department'}\"\n",
        "\n",
        "def add_career_opportunity(title, department, description, requirements, salary_range, location):\n",
        "    \"\"\"Add a new career opportunity\"\"\"\n",
        "    print(f\"DATABASE TOOL CALLED: Adding career opportunity - {title}\", flush=True)\n",
        "    with sqlite3.connect(DB) as conn:\n",
        "        cursor = conn.cursor()\n",
        "        cursor.execute('''\n",
        "            INSERT INTO career_opportunities (title, department, description, requirements, salary_range, location, is_active)\n",
        "            VALUES (?, ?, ?, ?, ?, ?, 1)\n",
        "        ''', (title, department, description, requirements, salary_range, location))\n",
        "        conn.commit()\n",
        "    return f\"Successfully added career opportunity: {title} in {department}\"\n",
        "\n",
        "print(\"✅ Tool functions defined!\")\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "🔧 Tools configured:\n",
            "   - get_radio_station_costs: Get advertising costs (spot ad and sponsorship) for a specific radio station.\n",
            "   - set_radio_station_costs: Set advertising costs for a specific radio station.\n",
            "   - get_career_opportunities: Get available career opportunities, optionally filtered by department.\n",
            "   - add_career_opportunity: Add a new career opportunity to the database.\n"
          ]
        }
      ],
      "source": [
        "# Tool definitions for OpenAI\n",
        "get_radio_costs_function = {\n",
        "    \"name\": \"get_radio_station_costs\",\n",
        "    \"description\": \"Get advertising costs (spot ad and sponsorship) for a specific radio station.\",\n",
        "    \"parameters\": {\n",
        "        \"type\": \"object\",\n",
        "        \"properties\": {\n",
        "            \"station_name\": {\n",
        "                \"type\": \"string\",\n",
        "                \"description\": \"The name of the radio station (Kiss FM, Classic 105, Radio Jambo, Homeboyz Radio, Gukena FM)\",\n",
        "            },\n",
        "        },\n",
        "        \"required\": [\"station_name\"],\n",
        "        \"additionalProperties\": False\n",
        "    }\n",
        "}\n",
        "\n",
        "set_radio_costs_function = {\n",
        "    \"name\": \"set_radio_station_costs\",\n",
        "    \"description\": \"Set advertising costs for a specific radio station.\",\n",
        "    \"parameters\": {\n",
        "        \"type\": \"object\",\n",
        "        \"properties\": {\n",
        "            \"station_name\": {\n",
        "                \"type\": \"string\",\n",
        "                \"description\": \"The name of the radio station\",\n",
        "            },\n",
        "            \"spot_ad_cost\": {\n",
        "                \"type\": \"number\",\n",
        "                \"description\": \"The new spot ad cost\",\n",
        "            },\n",
        "            \"sponsorship_cost\": {\n",
        "                \"type\": \"number\",\n",
        "                \"description\": \"The new sponsorship cost\",\n",
        "            },\n",
        "        },\n",
        "        \"required\": [\"station_name\", \"spot_ad_cost\", \"sponsorship_cost\"],\n",
        "        \"additionalProperties\": False\n",
        "    }\n",
        "}\n",
        "\n",
        "get_careers_function = {\n",
        "    \"name\": \"get_career_opportunities\",\n",
        "    \"description\": \"Get available career opportunities, optionally filtered by department.\",\n",
        "    \"parameters\": {\n",
        "        \"type\": \"object\",\n",
        "        \"properties\": {\n",
        "            \"department\": {\n",
        "                \"type\": \"string\",\n",
        "                \"description\": \"The department to filter by (optional)\",\n",
        "            },\n",
        "        },\n",
        "        \"required\": [],\n",
        "        \"additionalProperties\": False\n",
        "    }\n",
        "}\n",
        "\n",
        "add_career_function = {\n",
        "    \"name\": \"add_career_opportunity\",\n",
        "    \"description\": \"Add a new career opportunity to the database.\",\n",
        "    \"parameters\": {\n",
        "        \"type\": \"object\",\n",
        "        \"properties\": {\n",
        "            \"title\": {\n",
        "                \"type\": \"string\",\n",
        "                \"description\": \"The job title\",\n",
        "            },\n",
        "            \"department\": {\n",
        "                \"type\": \"string\",\n",
        "                \"description\": \"The department\",\n",
        "            },\n",
        "            \"description\": {\n",
        "                \"type\": \"string\",\n",
        "                \"description\": \"Job description\",\n",
        "            },\n",
        "            \"requirements\": {\n",
        "                \"type\": \"string\",\n",
        "                \"description\": \"Job requirements\",\n",
        "            },\n",
        "            \"salary_range\": {\n",
        "                \"type\": \"string\",\n",
        "                \"description\": \"Salary range\",\n",
        "            },\n",
        "            \"location\": {\n",
        "                \"type\": \"string\",\n",
        "                \"description\": \"Job location\",\n",
        "            },\n",
        "        },\n",
        "        \"required\": [\"title\", \"department\", \"description\", \"requirements\", \"salary_range\", \"location\"],\n",
        "        \"additionalProperties\": False\n",
        "    }\n",
        "}\n",
        "\n",
        "# List of available tools\n",
        "tools = [\n",
        "    {\"type\": \"function\", \"function\": get_radio_costs_function},\n",
        "    {\"type\": \"function\", \"function\": set_radio_costs_function},\n",
        "    {\"type\": \"function\", \"function\": get_careers_function},\n",
        "    {\"type\": \"function\", \"function\": add_career_function}\n",
        "]\n",
        "\n",
        "print(\"🔧 Tools configured:\")\n",
        "print(f\"   - {get_radio_costs_function['name']}: {get_radio_costs_function['description']}\")\n",
        "print(f\"   - {set_radio_costs_function['name']}: {set_radio_costs_function['description']}\")\n",
        "print(f\"   - {get_careers_function['name']}: {get_careers_function['description']}\")\n",
        "print(f\"   - {add_career_function['name']}: {add_career_function['description']}\")\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "✅ Tool call handler configured!\n"
          ]
        }
      ],
      "source": [
        "# Tool call handler\n",
        "def handle_tool_calls(message):\n",
        "    \"\"\"Handle multiple tool calls from the LLM\"\"\"\n",
        "    responses = []\n",
        "    for tool_call in message.tool_calls:\n",
        "        if tool_call.function.name == \"get_radio_station_costs\":\n",
        "            arguments = json.loads(tool_call.function.arguments)\n",
        "            station_name = arguments.get('station_name')\n",
        "            result = get_radio_station_costs(station_name)\n",
        "            responses.append({\n",
        "                \"role\": \"tool\",\n",
        "                \"content\": result,\n",
        "                \"tool_call_id\": tool_call.id\n",
        "            })\n",
        "        elif tool_call.function.name == \"set_radio_station_costs\":\n",
        "            arguments = json.loads(tool_call.function.arguments)\n",
        "            station_name = arguments.get('station_name')\n",
        "            spot_ad_cost = arguments.get('spot_ad_cost')\n",
        "            sponsorship_cost = arguments.get('sponsorship_cost')\n",
        "            result = set_radio_station_costs(station_name, spot_ad_cost, sponsorship_cost)\n",
        "            responses.append({\n",
        "                \"role\": \"tool\",\n",
        "                \"content\": result,\n",
        "                \"tool_call_id\": tool_call.id\n",
        "            })\n",
        "        elif tool_call.function.name == \"get_career_opportunities\":\n",
        "            arguments = json.loads(tool_call.function.arguments)\n",
        "            department = arguments.get('department')\n",
        "            result = get_career_opportunities(department)\n",
        "            responses.append({\n",
        "                \"role\": \"tool\",\n",
        "                \"content\": result,\n",
        "                \"tool_call_id\": tool_call.id\n",
        "            })\n",
        "        elif tool_call.function.name == \"add_career_opportunity\":\n",
        "            arguments = json.loads(tool_call.function.arguments)\n",
        "            title = arguments.get('title')\n",
        "            department = arguments.get('department')\n",
        "            description = arguments.get('description')\n",
        "            requirements = arguments.get('requirements')\n",
        "            salary_range = arguments.get('salary_range')\n",
        "            location = arguments.get('location')\n",
        "            result = add_career_opportunity(title, department, description, requirements, salary_range, location)\n",
        "            responses.append({\n",
        "                \"role\": \"tool\",\n",
        "                \"content\": result,\n",
        "                \"tool_call_id\": tool_call.id\n",
        "            })\n",
        "    return responses\n",
        "\n",
        "print(\"✅ Tool call handler configured!\")\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "✅ Chat function configured!\n"
          ]
        }
      ],
      "source": [
        "# Main chat function\n",
        "def chat(message, history):\n",
        "    \"\"\"Main chat function that handles tool calls\"\"\"\n",
        "    history = [{\"role\":h[\"role\"], \"content\":h[\"content\"]} for h in history]\n",
        "    messages = [{\"role\": \"system\", \"content\": system_message}] + history + [{\"role\": \"user\", \"content\": message}]\n",
        "    response = openai.chat.completions.create(model=MODEL, messages=messages, tools=tools)\n",
        "\n",
        "    # Handle tool calls in a loop to support multiple consecutive tool calls\n",
        "    while response.choices[0].finish_reason == \"tool_calls\":\n",
        "        message = response.choices[0].message\n",
        "        responses = handle_tool_calls(message)\n",
        "        messages.append(message)\n",
        "        messages.extend(responses)\n",
        "        response = openai.chat.completions.create(model=MODEL, messages=messages, tools=tools)\n",
        "    \n",
        "    return response.choices[0].message.content\n",
        "\n",
        "print(\"✅ Chat function configured!\")\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "✅ Sample data initialized!\n",
            "\n",
            "🧪 Testing the setup:\n",
            "DATABASE TOOL CALLED: Getting costs for Kiss FM\n",
            "Station: Kiss FM\n",
            "Spot Ad Cost: KSh 15,000.0\n",
            "Sponsorship Cost: KSh 500,000.0\n",
            "Description: Kenya's leading urban radio station\n",
            "\n",
            "==================================================\n",
            "\n",
            "DATABASE TOOL CALLED: Getting career opportunities for Sales\n",
            "Title: Sales Executive\n",
            "Department: Sales\n",
            "Description: Generate advertising revenue and build client relationships\n",
            "Requirements: Degree in Marketing/Business, 3+ years sales experience\n",
            "Salary: KSh 100,000 - 200,000\n",
            "Location: Nairobi\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Initialize sample data\n",
        "def initialize_sample_data():\n",
        "    \"\"\"Initialize the database with sample data\"\"\"\n",
        "    with sqlite3.connect(DB) as conn:\n",
        "        cursor = conn.cursor()\n",
        "        \n",
        "        # Clear existing data\n",
        "        cursor.execute('DELETE FROM radio_stations')\n",
        "        cursor.execute('DELETE FROM career_opportunities')\n",
        "        \n",
        "        # Insert radio stations data\n",
        "        radio_stations = [\n",
        "            (\"Kiss FM\", 15000, 500000, \"Kenya's leading urban radio station\"),\n",
        "            (\"Classic 105\", 12000, 800000, \"Kenya's premier classic hits station\"),\n",
        "            (\"Radio Jambo\", 10000, 1100000, \"Kenya's most popular vernacular station\"),\n",
        "            (\"Homeboyz Radio\", 8000, 150000, \"Kenya's youth-focused radio station\"),\n",
        "            (\"Gukena FM\", 6000, 100000, \"Kenya's leading vernacular station\")\n",
        "        ]\n",
        "        \n",
        "        cursor.executemany('''\n",
        "            INSERT INTO radio_stations (name, spot_ad_cost, sponsorship_cost, description)\n",
        "            VALUES (?, ?, ?, ?)\n",
        "        ''', radio_stations)\n",
        "        \n",
        "        # Insert career opportunities\n",
        "        careers = [\n",
        "            (\"Radio Presenter\", \"Programming\", \"Host radio shows and engage with listeners\", \"Degree in Media/Communication, 2+ years experience\", \"KSh 80,000 - 150,000\", \"Nairobi\", 1),\n",
        "            (\"Sales Executive\", \"Sales\", \"Generate advertising revenue and build client relationships\", \"Degree in Marketing/Business, 3+ years sales experience\", \"KSh 100,000 - 200,000\", \"Nairobi\", 1),\n",
        "            (\"Content Producer\", \"Programming\", \"Create engaging radio content and manage social media\", \"Degree in Media/Journalism, 2+ years experience\", \"KSh 70,000 - 120,000\", \"Nairobi\", 1),\n",
        "            (\"Technical Engineer\", \"Technical\", \"Maintain radio equipment and ensure smooth broadcasting\", \"Degree in Engineering, 3+ years technical experience\", \"KSh 90,000 - 160,000\", \"Nairobi\", 1),\n",
        "            (\"Marketing Manager\", \"Marketing\", \"Develop marketing strategies and manage brand campaigns\", \"Degree in Marketing, 5+ years experience\", \"KSh 150,000 - 250,000\", \"Nairobi\", 1),\n",
        "            (\"News Reporter\", \"News\", \"Research and report news stories for radio\", \"Degree in Journalism, 2+ years experience\", \"KSh 60,000 - 100,000\", \"Nairobi\", 1),\n",
        "            (\"Digital Media Specialist\", \"Digital\", \"Manage digital platforms and online content\", \"Degree in Digital Media, 2+ years experience\", \"KSh 80,000 - 140,000\", \"Nairobi\", 1)\n",
        "        ]\n",
        "        \n",
        "        cursor.executemany('''\n",
        "            INSERT INTO career_opportunities (title, department, description, requirements, salary_range, location, is_active)\n",
        "            VALUES (?, ?, ?, ?, ?, ?, ?)\n",
        "        ''', careers)\n",
        "        \n",
        "        conn.commit()\n",
        "    print(\"✅ Sample data initialized!\")\n",
        "\n",
        "# Initialize sample data\n",
        "initialize_sample_data()\n",
        "\n",
        "# Test the setup\n",
        "print(\"\\n🧪 Testing the setup:\")\n",
        "print(get_radio_station_costs(\"Kiss FM\"))\n",
        "print(\"\\n\" + \"=\"*50 + \"\\n\")\n",
        "print(get_career_opportunities(\"Sales\"))\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Launch the Radio Africa Products Chatbot\n",
        "\n",
        "The chatbot is now ready with comprehensive features for Radio Africa Products!\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "🚀 Launching Radio Africa Products Chatbot...\n",
            "📋 Available features:\n",
            "   - Get radio station advertising costs\n",
            "   - Set radio station advertising costs\n",
            "   - View career opportunities\n",
            "   - Add new career opportunities\n",
            "\n",
            "🎯 Example queries:\n",
            "   - 'What are the advertising costs for Kiss FM?'\n",
            "   - 'Show me career opportunities in Sales'\n",
            "   - 'Set the costs for Classic 105 to 15000 spot ads and 60000 sponsorship'\n",
            "   - 'What career opportunities are available?'\n",
            "   - 'Add a new job: Marketing Coordinator in Marketing department'\n",
            "* Running on local URL:  http://127.0.0.1:7860\n",
            "* To create a public link, set `share=True` in `launch()`.\n"
          ]
        },
        {
          "data": {
            "text/html": [
              "<div><iframe src=\"http://127.0.0.1:7860/\" width=\"100%\" height=\"500\" allow=\"autoplay; camera; microphone; clipboard-read; clipboard-write;\" frameborder=\"0\" allowfullscreen></iframe></div>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/plain": []
          },
          "execution_count": 10,
          "metadata": {},
          "output_type": "execute_result"
        },
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "DATABASE TOOL CALLED: Adding career opportunity - Marketing Coordinator\n"
          ]
        }
      ],
      "source": [
        "# Launch the Gradio interface\n",
        "print(\"🚀 Launching Radio Africa Products Chatbot...\")\n",
        "print(\"📋 Available features:\")\n",
        "print(\"   - Get radio station advertising costs\")\n",
        "print(\"   - Set radio station advertising costs\")\n",
        "print(\"   - View career opportunities\")\n",
        "print(\"   - Add new career opportunities\")\n",
        "print(\"\\n🎯 Example queries:\")\n",
        "print(\"   - 'What are the advertising costs for Kiss FM?'\")\n",
        "print(\"   - 'Show me career opportunities in Sales'\")\n",
        "print(\"   - 'Set the costs for Classic 105 to 15000 spot ads and 60000 sponsorship'\")\n",
        "print(\"   - 'What career opportunities are available?'\")\n",
        "print(\"   - 'Add a new job: Marketing Coordinator in Marketing department'\")\n",
        "\n",
        "interface = gr.ChatInterface(\n",
        "    fn=chat, \n",
        "    type=\"messages\",\n",
        "    title=\"Radio Africa Products Assistant\",\n",
        "    description=\"Ask me about career opportunities, radio station costs, and Radio Africa Products!\",\n",
        "    examples=[\n",
        "        \"What are the advertising costs for Kiss FM?\",\n",
        "        \"Show me career opportunities in Sales\",\n",
        "        \"Set the costs for Classic 105 to 15000 spot ads and 60000 sponsorship\",\n",
        "        \"What career opportunities are available?\",\n",
        "        \"Add a new job: Marketing Coordinator in Marketing department\"\n",
        "    ]\n",
        ")\n",
        "\n",
        "interface.launch()\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Key Implementation Features\n",
        "\n",
        "### 🎯 **Radio Station Management**\n",
        "- **5 Radio Stations**: Kiss FM, Classic 105, Radio Jambo, Homeboyz Radio, Gukena FM\n",
        "- **Cost Management**: Get and set spot ad costs and sponsorship costs\n",
        "- **Station Information**: Descriptions and details for each station\n",
        "\n",
        "### 💼 **Career Opportunities Management**\n",
        "- **Job Listings**: View all available positions\n",
        "- **Department Filtering**: Filter by specific departments (Sales, Programming, Technical, etc.)\n",
        "- **Job Management**: Add new career opportunities\n",
        "- **Detailed Information**: Job descriptions, requirements, salary ranges, locations\n",
        "\n",
        "### 🗄️ **Database Schema (ral.db)**\n",
        "```sql\n",
        "-- Radio Stations Table\n",
        "CREATE TABLE radio_stations (\n",
        "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "    name TEXT UNIQUE NOT NULL,\n",
        "    spot_ad_cost REAL NOT NULL,\n",
        "    sponsorship_cost REAL NOT NULL,\n",
        "    description TEXT\n",
        ");\n",
        "\n",
        "-- Career Opportunities Table  \n",
        "CREATE TABLE career_opportunities (\n",
        "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "    title TEXT NOT NULL,\n",
        "    department TEXT NOT NULL,\n",
        "    description TEXT,\n",
        "    requirements TEXT,\n",
        "    salary_range TEXT,\n",
        "    location TEXT,\n",
        "    is_active BOOLEAN DEFAULT 1\n",
        ");\n",
        "```\n",
        "\n",
        "### 🔧 **Tool Functions**\n",
        "1. **get_radio_station_costs**: Query advertising costs for specific stations\n",
        "2. **set_radio_station_costs**: Update advertising costs for stations\n",
        "3. **get_career_opportunities**: View job opportunities (with optional department filter)\n",
        "4. **add_career_opportunity**: Add new job postings\n",
        "\n",
        "### 🚀 **Usage Examples**\n",
        "- **Get Costs**: \"What are the advertising costs for Kiss FM?\"\n",
        "- **Set Costs**: \"Set the costs for Classic 105 to 15000 spot ads and 60000 sponsorship\"\n",
        "- **View Jobs**: \"Show me career opportunities in Sales\"\n",
        "- **Add Jobs**: \"Add a new job: Marketing Coordinator in Marketing department\"\n",
        "\n",
        "This implementation demonstrates comprehensive tool integration for a real-world business application!\n"
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": ".venv",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.12.12"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 2
}
